#!/usr/bin/python
# -*- coding: UTF-8 -*-
"""
Python 3.x
SQLAlchemy 1.4.41

SQLAlchemy 表关系
`ORM`层面的`CASCADE`
"""
from sqlalchemy import Column, Integer, String, Text
from constants import DB_URI
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

engine = create_engine(DB_URI, echo=True)

# 所有的类都要继承自`declarative_base`这个函数生成的基类
Base = declarative_base(engine)

from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref


class Address(Base):
    __tablename__ = 'address'
    id = Column(Integer, primary_key=True)
    email_address = Column(String(50), nullable=False)
    # Author表的外键，指定外键的时候，是使用的是数据库表的名称，而不是类名
    author_id = Column(Integer, ForeignKey('author.id'))
    # 在ORM层面绑定两者之间的关系，第一个参数是绑定的表的类名，
    # 第二个参数backref是通过Author反向访问时的字段名称
    author = relationship('Author', backref=backref("address",
                                                    cascade="save-update, delete, delete-orphan, merge, expunge"),
                          cascade="save-update, delete", single_parent=True)

    def __repr__(self):
        return "<Address(id='%s', email_address='%s', author_id='%s')>" % (self.id, self.email_address, self.author_id)


class Author(Base):
    __tablename__ = 'author'
    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), nullable=False)

    def __repr__(self):
        return "<Author(id='%s', username='%s')>" % (self.id, self.username)


# 映射到数据库
Base.metadata.drop_all()
Base.metadata.create_all()

Session = sessionmaker(bind=engine)
session = Session()

author = Author(username="YiYeBiJi")
address = Address(email_address='YiYeBiJi@126.com')
address.author = author
session.add(address)
session.commit()

# backref=("address", cascade="")
tmp_article = session.query(Author).all()
print(tmp_article)
# > []
tmp_address = session.query(Address).all()
print(tmp_address)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='None')>]

# backref=("address", cascade='save-update')
tmp_article = session.query(Author).all()
print(tmp_article)
# > [<Author(id='1', username='YiYeBiJi')>]
tmp_address = session.query(Address).all()
print(tmp_address)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='1')>]

# backref=("address", cascade='save-update, delete')
try:
    del_address = session.query(Address).first()
    session.delete(del_address)
    session.commit()
    tmp_article = session.query(Author).all()
    print(tmp_article)
except Exception as e:
    print(e)
# > []

# backref=("address", cascade="save-update, delete", single_parent=True)
try:
    del_author = session.query(Author).first()
    del_author.address = []
    session.commit()
    tmp_address = session.query(Address).all()
    print(tmp_address)
except Exception as e:
    print(e)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='None')>]

# backref=backref("address", cascade="save-update, delete, delete-orphan"),
# cascade="save-update, delete", single_parent=True
try:
    del_author = session.query(Author).first()
    del_author.address = []
    session.commit()
    tmp_address = session.query(Address).all()
    print(tmp_address)
except Exception as e:
    print(e)
# > []

# backref=backref("address", cascade="save-update, delete, delete-orphan, merge"),
# cascade="save-update, delete", single_parent=True
author = Author(username="YiYeBiJi")
address = Address(email_address='YiYeBiJi@126.com')
address.author = author
session.add(address)
session.commit()

author = Author(id=2, username="YiYeBiJi02")
address = Address(id=1, email_address='YiYeBiJi@126.com')
author.address.append(address)
session.merge(author)
session.commit()

tmp_article = session.query(Author).all()
print(tmp_article)
# > [<Author(id='2', username='YiYeBiJi02')>]
tmp_address = session.query(Address).all()
print(tmp_address)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='2')>]

# backref=backref("address", cascade="save-update, delete, delete-orphan, merge, expunge"),
# cascade="save-update, delete", single_parent=True)
author = Author(username="YiYeBiJi")
address = Address(email_address='YiYeBiJi@126.com')
address.author = author
session.add(address)
session.expunge(address)
session.commit()

tmp_article = session.query(Author).all()
print(tmp_article)
# > [<Author(id='2', username='YiYeBiJi02')>, <Author(id='3', username='YiYeBiJi')>]
tmp_address = session.query(Address).all()
print(tmp_address)
# > [<Address(id='1', email_address='YiYeBiJi@126.com', author_id='2')>]

session.close()
